package asgn03;

import java.io.*;
import java.sql.*;
import java.util.*;

public class Main {
	public static String sanitizeString(String s) {
		if (s.length() < 1) {
			s = " ";
		}
		if (s.charAt(0) != '\'') {
			s = "\'" + s;
		}
		s = s.replaceAll("\\s+$", "");	//removes trailing spaces
		for (int i = 1; i < s.length() - 2; i++) {	//check pairs of single quotes
			if (s.charAt(i) == '\'') {
				if (s.charAt(i+1) != '\'') {		//check escape character
					s = s.substring(0,i+1) + "\'" + s.substring(i+1,s.length());
					i++;
				}
			}
		}
		if (s.charAt(s.length()-1) != '\'') {
			s = s + "\'";
		}
		return s;
	}

	/**
	 * @param args Takes a String used to connect to the database; args[0] is the username and args[1] is the password. 
	 */
	public static void main(String[] args) throws IOException {
		if (args.length < 2) {
			System.out.println("Error: Username and password of the connecting database must be arguments to main.");
		}
		else {
			String username = args[0];
			String password = args[1];
			Connection sqlca  = null;
			Statement sqlStatement  = null;
			ResultSet rset = null;
			boolean run = true;
			boolean printOutput = true;
			boolean packageFound = false;
			boolean routeFound = false;
			boolean firstLeg = true;
			boolean firstPackage = true;
			boolean noRoute = false;
			Scanner scan = new Scanner(System.in);
			String input;
			String sender, day, time, query;									//for package queries
			String receiver, source, destination, timeDepart, dayDepart;		//for query results
			String srcCity, destCity, rootCity;
			int tnum = -1, id = -1, dayInt = 0, packageWeight, i;
			double lastTimeDepart = 0, lastDuration = 0;
			String duration = "-1";
			Stack<String> srcStack, destStack;
			LinkedList<String> route, routeSQL;
			String[] strRoute, strRouteSQL;
			boolean enterPackage = true;
			
			try{
				DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
				sqlca= DriverManager.getConnection ("jdbc:oracle:thin:@//127.0.0.1:1521/XE", username, password);
				sqlStatement = sqlca.createStatement();
				
				while  (run) {
					if (printOutput && !enterPackage) {
						System.out.println("(1) Query for a package route");
						System.out.println("(2) Enter a new package");
						System.out.println("(3) Turn output off");
						System.out.println("(4) Turn output on");
						System.out.println("(5) Quit");
					}
					if (printOutput && enterPackage) {
						System.out.println("Enter the package sender (q to quit to the main menu):");
					}
					input = scan.nextLine();
					if (!enterPackage && input.equals("1")) {
						firstPackage = true;
						packageFound = false;
						routeFound = false;
						tnum = -1;
						if (printOutput) {
							System.out.println("Who was the sender?");
						}
						input = scan.nextLine();
						if (input.equalsIgnoreCase("q")) {
							continue;
						}
						sender = sanitizeString(input);
						if (printOutput) {
							System.out.println("What day was the package sent?");
						}
						input = scan.nextLine();
						if (input.equalsIgnoreCase("q")) {
							continue;
						}
						day = sanitizeString(input);
						if (printOutput) {
							System.out.println("What time was the package sent?");
						}
						input = scan.nextLine();
						if (input.equalsIgnoreCase("q")) {
							continue;
						}
						time = sanitizeString(input);
						//query Package
						//special case, if source=destination
						query = "SELECT TNum, Sender, Receiver, to_char(TimeRec,'hh24:mi:ss') AS Time, " +
							"to_char(DayRec,'Mon dd yyyy') as Day " + 
							"FROM Package " + 
							"WHERE sender=" + sender + " AND to_char(TimeRec,'hh24:mi:ss')=" + time + 
							" AND DayRec=to_date(" + day + ",'MON DD YYYY') AND TNum NOT IN " +
							"(SELECT TNum FROM SchedOn)";
						rset = sqlStatement.executeQuery(query);
						while (rset.next())
						{
							if (firstPackage) {					//output formatting
								System.out.println();
							}
							System.out.println("Package " + rset.getInt("TNum") + " from " + rset.getString("Sender")
									+ " to " + rset.getString("Receiver") + " was sent at " + rset.getString("Time")
									+ ", " + rset.getString("Day"));
							System.out.println("There is no need to route for the package.\n");
							packageFound = true;
							firstPackage = false;
						}
						query = "SELECT Sender, Receiver, to_char(TimeRec,'HH24:MI:SS') as TimeRec,"
								+ " to_char(DayRec,'Mon DD YYYY') as DayRec,"
								+ " SchedOn.TNum, SchedOn.DayInt, Hop.ID, FromCity, ToCity, Duration,"
								+ " to_char(TimeDepart,\'HH24:MI:SS\') as TimeDepart,"
								+ " to_char(Day,\'Mon DD YYYY\') as Day"
							+ " FROM (Hop JOIN Leg ON Hop.ID=Leg.ID)"
								+ " JOIN (SchedOn JOIN Package ON SchedOn.TNum=Package.TNum)"
								+ " ON (Hop.ID=SchedOn.ID AND SchedOn.DayInt=Leg.DayInt)"
							+ " WHERE (Leg.ID, Leg.DayInt) IN"
								+ " (SELECT ID, DayInt"					//gets the IDs for the specified packages
								+ " FROM SchedOn"
								+ " WHERE TNum IN"				//gets packages for given input
									+ " (SELECT TNum"
									+ " FROM Package"
									+ " WHERE Sender = " + sender
											+ " AND DayRec = to_date(" + day + ",\'MON DD YYYY\')"
											+ " AND to_char(TimeRec,\'HH24:MI:SS\') = " + time
									+ ")"
								+ ")"
							+ " AND Sender = " + sender
							+ " ORDER BY TNum, DayInt, TimeDepart";
						//System.out.println("1:\t" + query);	//debug
						rset = sqlStatement.executeQuery(query);
						while (rset.next()) { //for each package that matches the query
							duration = rset.getString("Duration");
							if (tnum != rset.getInt("TNum")) {		//for each new package summary
								if (!firstPackage && routeFound) {				//format output between package summaries
									System.out.println(" and arriving " + duration + " minutes later.\n");
								}
								tnum = rset.getInt("TNum");
								if (firstPackage) {
									System.out.println();
								}
								System.out.println("Package " + tnum + " from " + rset.getString("Sender")
										+ " to " + rset.getString("Receiver") + " was sent at "
										+ rset.getString("TimeRec") + ", " + rset.getString("DayRec") + ".\n");
								System.out.println("The route chosen is as follows:");
								firstPackage = false;
								firstLeg = true;
							}
							if (!firstLeg) {						//format output between consecutive Leg statements
								System.out.println(".");
							}
							firstLeg = false;
							System.out.print("Leg " + rset.getString("ID") + " from " + rset.getString("FromCity") + " to "
									+ rset.getString("ToCity") + ", leaving at " + rset.getString("TimeDepart")
									+ ", " + rset.getString("Day"));
							packageFound = true;
							routeFound = true;
						}
						if (!packageFound) {
							System.out.println("\nPackage not found.\n");
						}
						else if (routeFound) {										//conclude the summary output
							System.out.println(" and arriving " + duration + " minutes later.\n");
						}
					}
					else if (enterPackage || input.equals("2")) {
						enterPackage = true;
						if (input.equalsIgnoreCase("q")) {
							enterPackage = false;
							continue;
						}
						if (input.equals("2")) {
							if (printOutput) {
								System.out.println("Enter the package sender (q to quit to the main menu):");
							}
							input = scan.nextLine();
							if (input.equalsIgnoreCase("q")) {
								enterPackage = false;
								continue;
							}
						}
						sender = sanitizeString(input);
						if(printOutput)
							System.out.println("Enter the package receiver:");
						input = scan.nextLine();
						if (input.equalsIgnoreCase("q")) {
							enterPackage = false;
							continue;
						}
						receiver = sanitizeString(input);
						if(printOutput)
							System.out.println("Enter the source city:");
						input = scan.nextLine();
						if (input.equalsIgnoreCase("q")) {
							enterPackage = false;
							continue;
						}
						source = sanitizeString(input);
						if(printOutput)
							System.out.println("Enter the destination city:");
						input = scan.nextLine();
						if (input.equalsIgnoreCase("q")) {
							enterPackage = false;
							continue;
						}
						destination = sanitizeString(input);
						if(printOutput)
							System.out.println("Enter the package weight:");
						input = scan.nextLine();
						if (input.equalsIgnoreCase("q")) {
							enterPackage = false;
							continue;
						}
						packageWeight = Integer.parseInt(input);
						if(printOutput)
							System.out.println("What day is it?");
						input = scan.nextLine();
						if (input.equalsIgnoreCase("q")) {
							enterPackage = false;
							continue;
						}
						dayDepart = sanitizeString(input);
						if(printOutput)
							System.out.println("What time is it?");
						input = scan.nextLine();
						if (input.equalsIgnoreCase("q")) {
							enterPackage = false;
							continue;
						}
						timeDepart = sanitizeString(input);
						
						routeSQL = new LinkedList<String>();
						query = "SELECT MAX(TNum) AS Maxtnum FROM Package";	//inserting package details into Package table
						rset = sqlStatement.executeQuery(query);
						if(rset.next())
							tnum = rset.getInt("Maxtnum") + 1;
						else
							tnum = 1;
						query = "INSERT INTO Package VALUES(" + tnum + ", " + sender + ", " + receiver + ", to_date(" + timeDepart + ",'hh24:mi:ss'), to_date(" + dayDepart.toUpperCase() + ",'MON dd yyyy'), " + packageWeight + ")"; 
						routeSQL.add(query);
						if(source.equals(destination))
						{
							sqlStatement.executeUpdate(query);
							if (printOutput)
								System.out.println("There is no need to route the package.");
							continue;
						}
						//computing route
						srcCity = source;
						destCity = destination;
						rootCity = "\'Miami\'";
						srcStack = new Stack<String>();
						destStack = new Stack<String>();
						while(!srcCity.equals(rootCity))
						{
							query = "SELECT Big FROM Serves WHERE Small=" + srcCity;
							rset = sqlStatement.executeQuery(query);
							if(rset.next())
							{
								srcStack.push(srcCity);
								srcCity = "\'" + rset.getString("Big") + "\'";
							}
							else
							{
								if (printOutput)
									System.out.println(srcCity + " is not serviced.");
								break;
							}
						}
						while(!destCity.equals(rootCity))
						{
							query = "SELECT Big FROM Serves WHERE Small=" + destCity;
							rset = sqlStatement.executeQuery(query);
							if(rset.next())
							{
								destStack.push(destCity);
								destCity = "\'" + rset.getString("Big") + "\'";
							}
							else
							{
								if (printOutput)
									System.out.println(destCity + " is not serviced.");
								break;
							}
						}
//						System.out.println(srcStack.size() + "    " + destStack.size());//debug
						while(srcCity.equals(destCity) && (!srcStack.isEmpty() || !destStack.isEmpty()))
						{
							rootCity = srcCity;
							if(!srcStack.isEmpty())
								srcCity = srcStack.pop();
							if(!destStack.isEmpty())
								destCity = destStack.pop();
						}
						route = new LinkedList<String>();
						if(!srcCity.equals(rootCity))
							route.add(srcCity);
						route.add(rootCity);
						if(!destCity.equals(rootCity))
							route.add(destCity);
						while(!destStack.isEmpty())
							route.add(destStack.pop());
						while(!srcStack.isEmpty())
							route.addFirst(srcStack.pop());
						strRoute = (String[])route.toArray(new String[0]);
						
//						for(i=0;i<strRoute.length;i++)//debug
//							System.out.println(strRoute[i]);//debug
						
						//computing legs
						query = "SELECT Hop.ID, DayInt, (to_char(timedepart,'hh24')/24+to_char(timedepart,'mi')/1440+to_char(timedepart,'ss')/86400) AS TD, Duration, to_char(TimeDepart,'hh24:mi:ss') AS TimeOfDeparture " +
						"FROM Hop JOIN Leg ON Hop.ID=Leg.ID " + 
						"WHERE FromCity=" + strRoute[0] + " AND ToCity=" + strRoute[1] + " AND Day=to_date(" + dayDepart.toUpperCase() + ",'MON dd yyyy') AND to_char(TimeDepart,'hh24:mi:ss')>to_char(to_date(" + timeDepart + ",'hh24:mi:ss')+(1/12),'hh24:mi:ss') AND (CurrentLbs+" + packageWeight + ")<= Capacity " +
						"UNION " +
						"SELECT Hop.ID, DayInt, (to_char(timedepart,'hh24')/24+to_char(timedepart,'mi')/1440+to_char(timedepart,'ss')/86400) AS TD, Duration, to_char(TimeDepart,'hh24:mi:ss') AS TimeOfDeparture " +
						"FROM Hop JOIN Leg ON Hop.ID=Leg.ID " + 
						"WHERE FromCity=" + strRoute[0] + " AND ToCity=" + strRoute[1] + " AND Day>to_date(" + dayDepart.toUpperCase() + ",'MON dd yyyy') AND (CurrentLbs+" + packageWeight + ")<= Capacity " +
						"ORDER BY 2, 5";
						rset = sqlStatement.executeQuery(query);
						if(rset.next())
						{
							query = "INSERT INTO SchedOn VALUES(" + tnum + "," + rset.getInt("ID") + "," + rset.getInt("DayInt") + ")";
							routeSQL.add(query);
							query = "UPDATE Leg SET CurrentLbs=CurrentLbs+" + packageWeight + " WHERE ID=" + rset.getInt("ID") + " AND DayInt=" + rset.getInt("DayInt");
							routeSQL.add(query);
							dayInt = rset.getInt("DayInt");
							lastDuration = rset.getDouble("Duration");
							lastTimeDepart = rset.getDouble("TD");
							id = rset.getInt("ID");
//							System.out.println(id);//debug
						}
						else
							noRoute = true;
						if(!noRoute)
						{
							for(i = 1; i < strRoute.length - 1; i++)
							{
								//TODO: leg computation, source+1 to destination (should be done)
								if(lastTimeDepart + (lastDuration / 1440) + (1.0 / 32) < 1)//same day
								{
									query = "SELECT Hop.ID, DayInt, (to_char(timedepart,'hh24')/24+to_char(timedepart,'mi')/1440+to_char(timedepart,'ss')/86400) AS TD, Duration, to_char(TimeDepart,'hh24:mi:ss') AS TimeOfDeparture " +
									"FROM Hop JOIN Leg ON Hop.ID=Leg.ID " + 
									"WHERE FromCity=" + strRoute[i] + " AND ToCity=" + strRoute[i+1] + " AND DayInt=" + dayInt + " AND to_char(TimeDepart,'hh24:mi:ss')>(to_char(Day+" + (lastTimeDepart+(lastDuration/1440)) + "+(1.0/32),'hh24:mi:ss')) AND (CurrentLbs+" + packageWeight + ")<= Capacity " +
									"UNION " +
									"SELECT Hop.ID, DayInt, (to_char(timedepart,'hh24')/24+to_char(timedepart,'mi')/1440+to_char(timedepart,'ss')/86400) AS TD, Duration, to_char(TimeDepart,'hh24:mi:ss') AS TimeOfDeparture " +
									"FROM Hop JOIN Leg ON Hop.ID=Leg.ID " + 
									"WHERE FromCity=" + strRoute[i] + " AND ToCity=" + strRoute[i+1] + " AND DayInt>" + dayInt + " AND (CurrentLbs+" + packageWeight + ")<= Capacity " +
									"ORDER BY 2, 5";
								}
								else//next day
								{
									query = "SELECT Hop.ID, DayInt, (to_char(timedepart,'hh24')/24+to_char(timedepart,'mi')/1440+to_char(timedepart,'ss')/86400) AS TD, Duration, to_char(TimeDepart,'hh24:mi:ss') as TimeOfDeparture " +
									"FROM Hop JOIN Leg ON Hop.ID=Leg.ID " + 
									"WHERE FromCity=" + strRoute[i] + " AND ToCity=" + strRoute[i+1] + " AND DayInt=" + dayInt + "+1 AND to_char(TimeDepart,'hh24:mi:ss')>(to_char(Day+" + (lastTimeDepart+(lastDuration/1440)) + "+(1.0/32),'hh24:mi:ss')) AND (CurrentLbs+" + packageWeight + ")<= Capacity " +
									"UNION " +
									"SELECT Hop.ID, DayInt, (to_char(timedepart,'hh24')/24+to_char(timedepart,'mi')/1440+to_char(timedepart,'ss')/86400) AS TD, Duration, to_char(TimeDepart,'hh24:mi:ss') AS TimeOfDeparture " +
									"FROM Hop JOIN Leg ON Hop.ID=Leg.ID " + 
									"WHERE FromCity=" + strRoute[i] + " AND ToCity=" + strRoute[i+1] + " AND DayInt>" + dayInt + "+1 AND (CurrentLbs+" + packageWeight + ")<= Capacity " +
									"ORDER BY 2, 5";
								}
//								System.out.println(query);//debug
								rset = sqlStatement.executeQuery(query);
								if(rset.next())
								{
									query = "INSERT INTO SchedOn VALUES(" + tnum + "," + rset.getInt("ID") + "," + rset.getInt("DayInt") + ")";
									routeSQL.add(query);
									query = "UPDATE Leg SET CurrentLbs=CurrentLbs+" + packageWeight + " WHERE ID=" + rset.getInt("ID") + " AND DayInt=" + rset.getInt("DayInt");
									routeSQL.add(query);
									dayInt = rset.getInt("DayInt");
									lastDuration = rset.getDouble("Duration");
									lastTimeDepart = rset.getDouble("TD");
									id = rset.getInt("ID");
								}
								else
								{
									noRoute = true;
									break;
								}
							}
						}
						if(noRoute)
						{
							if (printOutput)
								System.out.println("No route.");
							continue;
						}
						
						// execute all queued inserts/updates, since package can be routed
						strRouteSQL = (String[])routeSQL.toArray(new String[0]);
						for(i = 0; i < strRouteSQL.length; i++)
						{
//							System.out.println(i);//debug
//							System.out.println(strRouteSQL[i]);//debug
							sqlStatement.executeUpdate(strRouteSQL[i]);
						}
						//Package placement summary: The package will arrive at 06:35:00 on Dec 03, 2005.
						query = "SELECT to_char((TimeDepart + Duration/1440),'HH24:MI:SS') as TimeArrive, "
						+ "to_char((Day + to_char(TimeDepart,'HH24')/24 + (to_char(TimeDepart,'MI')+Duration)/1440 + to_char(TimeDepart,'SS')/86400),'Mon DD, YYYY') "
						+ " as DateArrive "
						+ "FROM Hop JOIN Leg ON Hop.ID = Leg.ID "
						+ "WHERE Hop.ID = " + id + " AND DayInt = " + dayInt;
						rset = sqlStatement.executeQuery(query);
						if (rset.next()) {
							if (printOutput) {
								System.out.println("\nThe package will arrive at "
										+ rset.getString("TimeArrive") + " on " + rset.getString("DateArrive") + ".\n");
							}
						}
						else {
							if (printOutput)
								System.out.println("Error in package placement summary.");
						}
					}
					else if (!enterPackage && input.equals("3")) {
						printOutput = false;
					}
					else if (!enterPackage && input.equals("4")) {
						printOutput = true;
					}
					else if (!enterPackage && input.equals("5")) {
						run = false;
						return;
					}
				}
				sqlStatement.close();
				sqlca.close();
			}
			catch (SQLException ex)                  
			{ 
				System.out.println("SQLException:" + ex.getMessage() + "<BR>");	    
				//ex.printStackTrace();
			}
		}
	}
}